package com.bw.mapper;

import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import com.bw.bean.Vo3;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {
    @Select("select hour(create_time)name,count(user_id)value from user_chapter_process\n" +
            "group by hour(create_time) order by hour(create_time)")
    List<Vo1> list1();
    @Select("select ci.course_name name,count(distinct t.user_id)num,avg(t.score)avg1,avg(t.duration_sec)avg2 from test_exam t left join test_paper tp on t.paper_id = tp.id\n" +
            "left join course_info ci on tp.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo2> list2();
    @Select("select count(*) from payment_info where date(create_time)>='2021-04-23'")
    Integer list3();
    @Select("select ci.course_name name,avg(r.review_stars)avg1,count(distinct r.user_id)num1,\n" +
            "       sum(case when r.review_stars=5 then 1 else 0 end)num2,\n" +
            "       sum(case when r.review_stars=5 then 1 else 0 end)/count(*)hpl\n" +
            "       from review_info r left join course_info ci on r.course_id = ci.id\n" +
            "group by ci.course_name")
    List<Vo3> list4();
}
